﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using itau.imnk.entidades;
using EnterpriseLibraryWrapper;
using System.Data;

namespace itau.imnk.dados
{
    public class YieldDB : IYieldDAO
    {
        public IList<Informacao> ObterInformacoesYieldDiaria(string nomeMercado, string nomeYield, DateTime dataReferencia)
        {
            IList<Informacao> listaRetorno;
            DataAccessSQL data = null;
            DataSet ds;

            try
            {
                listaRetorno = new List<Informacao>();

                data = DataAccessSQL.GetDataAccess();

                string sqlCommand = string.Format(string.Concat(
                 "SELECT ",
                    "A.NM_YIELD, ",
                    "A.CD_TIPO_YIELD, ",
                    "A.DT_REFERENCIA, ",
                    "A.DT_ULT_ALT, ",
                    "B.CD_BACEN, ",
                    "B.CD_TIPO_INFORMACAO, ",
                    "B.CD_TIPO_PU_TAXA, ",
                    "B.CD_TIPO_TAXA, ",
                    "B.CD_YIELD, ",
                    "B.DT_ATUALIZACAO, ",
                    "B.DT_REG_HISTORICO, ",
                    "B.DT_VENCIMENTO, ",
                    "B.IC_CALCULO, ",
                    "B.IC_CONTINGENCIA, ",
                    "B.IC_LIQUIDO, ",
                    "B.MESREF, ",
                    "B.VL_PERC_CALCULO, ",
                    "B.VL_PU, ",
                    "B.VL_PU_TAXA, ",
                    "B.VL_PU_TAXA_252, ",
                    "B.VL_SPREAD, ",
                    "B.VL_TAXA_FORWARD ",
                "FROM TB_YIELD A (nolock) ",
                    "INNER JOIN TB_YIELD_INFORMACAO B (nolock) ",
                "ON ",
                    "A.CD_YIELD = B.CD_YIELD ",
                "WHERE ",
                    "A.DT_REFERENCIA = (select MAX(B.DT_REFERENCIA) FROM ",
                    "TB_YIELD B WHERE B.NM_YIELD = '{1}' AND B.CD_TIPO_YIELD = '{2}') AND ",
                    "A.NM_YIELD = '{1}' AND ",
                    "A.CD_TIPO_YIELD = '{2}' ",
                "ORDER BY ",
                    "A.DT_REFERENCIA, ",
                    "A.CD_TIPO_YIELD, ",
                    "B.CD_TIPO_INFORMACAO, ",
                    "B.DT_VENCIMENTO"), 
                    dataReferencia.ToString("yyyy/MM/dd"),
                    nomeYield,
                    nomeMercado);

                ds = data.ExecuteDataSetSqlCommand(sqlCommand);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dataRow in ds.Tables[0].Rows)
                    {
                        listaRetorno.Add(EntidadeFactory.GetYieldInformacao(dataRow));
                    }
                }

                return listaRetorno;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                data.Dispose();
                data = null;
                ds = null;
            }
        }
    }
}
